package easybooking.db;

import easybooking.data.Airport;
import easybooking.data.AirportImpl;
import java.rmi.RemoteException;
import java.util.Vector;
import java.sql.*;

/**
 * Mapper-Class, which displays <code>Airport</code> objects in a relational
 * database. Therefore a collection of methods to search, create and modify
 * will be provided.
 * The mapping is bidirectional, which means, that objects could be transformed
 * into database-structures and database-structures into objects.
 *
 * @author Mark Roman Stehle
 */
// <editor-fold defaultstate="collapsed" desc=" UML Marker ">
// #[regen=yes,id=DCE.AC617AB5-8DCD-E232-1A7E-2789C75EEF56]
// </editor-fold>
public class AirportMapper extends Mapper {

    /**
     * The class <code>AirportMapper</code> will be instantiated only once.
     * This is called <b>Singleton</b>, because of the <code>static</code>-tag,
     * the variable exists only once for all instances of this class. The
     * variable saves the only instance of this class.
     *
     * @see airportMapper()
     */
    // <editor-fold defaultstate="collapsed" desc=" UML Marker ">
    // #[regen=yes,id=DCE.7137B4C7-A17E-1C03-E027-D5F4CB98D981]
    // </editor-fold>
    private static AirportMapper airportMapper = null;

    /**
     * The protected constructor prevents the possibility to generate a new
     * instance of this class.
     */
    protected AirportMapper() {
    }

    /**
     * The static method can be called with
     * <code>AirportMapper.airportMapper()</code>.
     * This guarantees the singleton property by checking so that only one
     * instance of the <code>AirportMapper</code> exists.

     * <b>Conclusion:</b> The <code>AirportMapper</code> should not be
     * instantiated with <code>new</code>, but rather by calling the static
     * method.

     * @return The <code>AirportMapper</code> object.
     * @see airportMapper
     */
    // <editor-fold defaultstate="collapsed" desc=" UML Marker ">
    // #[regen=yes,id=DCE.B18D8D89-E420-1B7B-EF6E-F6A683846310]
    // </editor-fold>
    public static AirportMapper airportMapper() {
        if (airportMapper == null) {
            airportMapper = new AirportMapper();
        }
        return airportMapper;
    }

    /**
     * Searching an airport with given key, since the key is distinct only one
     * object will be returned.
     *
     * @param id primary key (->DB)
     * @return Airport object, which corresponds the given key,
     * <code>null</code> for a non existing database tupel.
     */
    // <editor-fold defaultstate="collapsed" desc=" UML Marker ">
    // #[regen=yes,id=DCE.E258E955-CA75-87F2-D3C6-3407F5C2AE55]
    // </editor-fold>
    public Airport findByKey(int id) {
        Connection con = DBConnection.connection();
        try {
            Statement stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery(
                    "SELECT airportId, name, initials, city, isActive FROM Airport " +
                    "WHERE airportId=" + id);
            if (rs.next()) {
                try {
                    Airport a = new AirportImpl();
                    a.setAirportId(rs.getInt("airportId"));
                    a.setName(rs.getString("name"));
                    a.setInitials(rs.getString("initials"));
                    a.setCity(rs.getString("city"));
                    a.setIsActive(rs.getBoolean("isActive"));
                    return a;
                } catch (Exception ex) {
                    ex.printStackTrace();
                    return null;
                }
            }
        } catch (SQLException e2) {
            e2.printStackTrace();
            return null;
        }
        return null;
    }

    /**
     * Selecting all active airports from the database.
     *
     * @return A vector of airport objects, which includes all active airports.
     * In case of an exception, an empty vector will be returned.
     */
    // <editor-fold defaultstate="collapsed" desc=" UML Marker ">
    // #[regen=yes,id=DCE.FA5FAD00-5304-57EF-5B91-1468A3DB1708]
    // </editor-fold>
    public Vector<Airport> findAll() {
        Connection con = DBConnection.connection();
        Vector<Airport> result = new Vector<Airport>();
        try {
            Statement stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery(
                    "SELECT airportId, name, initials, city, isActive FROM Airport " +
                    "WHERE isActive = true " +
                    "ORDER BY airportId ASC");
            while (rs.next()) {
                try {
                    Airport a = new AirportImpl();
                    a.setAirportId(rs.getInt("airportId"));
                    a.setName(rs.getString("name"));
                    a.setInitials(rs.getString("initials"));
                    a.setCity(rs.getString("city"));
                    a.setIsActive(rs.getBoolean("isActive"));
                    result.addElement(a);
                } catch (Exception ex) {
                    ex.printStackTrace();
                    break;
                }
            }
        } catch (SQLException e2) {
            e2.printStackTrace();
        }
        return result;
    }

    /**
     * Inserting an <code>Airport</code> object into the database. Before that,
     * the primary key of the given object will be verified und corrected.
     *
     * @param a the object to be saved
     * @return The given object, with corrected primary key.
     */
    // <editor-fold defaultstate="collapsed" desc=" UML Marker ">
    // #[regen=yes,id=DCE.891D813B-49E6-3A4C-A8F4-F520D4085066]
    // </editor-fold>
    public Airport insert(Airport a) {
        Connection con = DBConnection.connection();
        try {
            Statement stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery(
                    "SELECT MAX(airportId) AS maxAirportId " +
                    "FROM Airport");
            if (rs.next()) {
                try {
                    a.setAirportId(rs.getInt("maxAirportId") + 1);
                    stmt = con.createStatement();
                    stmt.executeUpdate("INSERT INTO Airport (airportId, name, initials, city, isActive) " +
                            "VALUES (" +
                            a.getAirportId() + ",'" +
                            a.getName() + "','" +
                            a.getInitials() + "','" +
                            a.getCity() + "','" +
                            booleanToInt(a.getIsActive()) + "')");
                } catch (RemoteException ex) {
                    ex.printStackTrace();
                }
            }
        } catch (SQLException e2) {
            e2.printStackTrace();
        }
        return a;
    }

    /**
     * Rewriting an object into the database.
     *
     * @param a the object which sould be written into the database
     * @return The given object.
     */
    // <editor-fold defaultstate="collapsed" desc=" UML Marker ">
    // #[regen=yes,id=DCE.232C3D57-F75C-4993-A4B6-92D15EE2F916]
    // </editor-fold>
    public Airport update(Airport a) {
        Connection con = DBConnection.connection();
        try {
            Statement stmt = con.createStatement();
            stmt.executeUpdate("UPDATE Airport " +
                    "SET name=\"" + a.getName() + "\", " +
                    "initials=\"" + a.getInitials() + "\", " +
                    "city=\"" + a.getCity() + "\", " +
                    "isActive=\"" + booleanToInt(a.getIsActive()) + "\" " +
                    "WHERE airportId=" + a.getAirportId());
        } catch (SQLException e2) {
            e2.printStackTrace();
        } catch (RemoteException e) {
            e.printStackTrace();
        }
        return a;
    }

    /**
     * Selecting all active airports from the database with a certain name,
     * which is given through a parameter.
     *
     * @param name the name or part of a name which should be searched for
     * @return A vector of airport objects, which includes all active airports
     * with a certain name.
     * In case of an exception, an empty vector will be returned.
     */
    // <editor-fold defaultstate="collapsed" desc=" UML Marker ">
    // #[regen=yes,id=DCE.39A65CE7-047A-1E88-308F-844882219EE6]
    // </editor-fold>
    public Vector<Airport> findByName(String name) {
        Connection con = DBConnection.connection();
        Vector<Airport> result = new Vector<Airport>();
        try {
            Statement stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery(
                    "SELECT airportId, name, initials, city, isActive FROM Airport " +
                    "WHERE name LIKE '%" + name + "%' " +
                    "AND isActive = true " +
                    "ORDER BY airlineId ASC");
            while (rs.next()) {
                try {
                    Airport a = new AirportImpl();
                    a.setAirportId(rs.getInt("airportId"));
                    a.setName(rs.getString("name"));
                    a.setInitials(rs.getString("initials"));
                    a.setCity(rs.getString("city"));
                    a.setIsActive(rs.getBoolean("isActive"));
                    result.addElement(a);
                } catch (Exception ex) {
                    ex.printStackTrace();
                    break;
                }
            }
        } catch (SQLException e2) {
            e2.printStackTrace();
        }
        return result;
    }

    /**
     * Selecting all active airports from the database with certain initials,
     * which are given through a parameter.
     *
     * @param initials the initials or part of the initials which should be
     * searched for
     * @return A vector of airport objects, which includes all active airports
     * with certain initials.
     * In case of an exception, an empty vector will be returned.
     */
    // <editor-fold defaultstate="collapsed" desc=" UML Marker ">
    // #[regen=yes,id=DCE.8064720D-6A3F-3D37-3075-5FC677AEADBD]
    // </editor-fold>
    public Vector<Airport> findByInitials(String initials) {
        Connection con = DBConnection.connection();
        Vector<Airport> result = new Vector<Airport>();
        try {
            Statement stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery(
                    "SELECT airportId, name, initials, city, isActive FROM Airport " +
                    "WHERE initials LIKE '%" + initials + "%' " +
                    "AND isActive = true " +
                    "ORDER BY airlineId ASC");
            while (rs.next()) {
                try {
                    Airport a = new AirportImpl();
                    a.setAirportId(rs.getInt("airportId"));
                    a.setName(rs.getString("name"));
                    a.setInitials(rs.getString("initials"));
                    a.setCity(rs.getString("city"));
                    a.setIsActive(rs.getBoolean("isActive"));
                    result.addElement(a);
                } catch (Exception ex) {
                    ex.printStackTrace();
                    break;
                }
            }
        } catch (SQLException e2) {
            e2.printStackTrace();
        }
        return result;
    }

    /**
     * Selecting all active airports from the database with a certain city,
     * which is given through a parameter.
     *
     * @param city the city or part of a city which should be searched for
     * @return A vector of airport objects, which includes all active airlines
     * with a certain city.
     * In case of an exception, an empty vector will be returned.
     */
    // <editor-fold defaultstate="collapsed" desc=" UML Marker ">
    // #[regen=yes,id=DCE.CCC42977-C907-685E-AFDF-E8BE0D8A5111]
    // </editor-fold>
    public Vector<Airport> findByCity(String city) {
        Connection con = DBConnection.connection();
        Vector<Airport> result = new Vector<Airport>();
        try {
            Statement stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery(
                    "SELECT airportId, name, initials, city, isActive FROM Airport " +
                    "WHERE city LIKE '%" + city + "%' " +
                    "AND isActive = true " +
                    "ORDER BY airlineId ASC");
            while (rs.next()) {
                try {
                    Airport a = new AirportImpl();
                    a.setAirportId(rs.getInt("airportId"));
                    a.setName(rs.getString("name"));
                    a.setInitials(rs.getString("initials"));
                    a.setCity(rs.getString("city"));
                    a.setIsActive(rs.getBoolean("isActive"));
                    result.addElement(a);
                } catch (Exception ex) {
                    ex.printStackTrace();
                    break;
                }
            }
        } catch (SQLException e2) {
            e2.printStackTrace();
        }
        return result;
    }
}

